USE Master

IF DB_ID (N'Department1') IS NOT NULL
DROP DATABASE Department1;
GO
CREATE DATABASE Department1
ON PRIMARY
( NAME = 'Department_dat',
	FILENAME = 'C:\Database\Department.mdf',
	SIZE = 10, 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 5 )
LOG ON
( NAME = 'Department_Log', 
	FILENAME = 'C:\Database\Department.ldf', 
	SIZE = 5MB, 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 5MB)
GO
-- Run Database Department
USE Department;
GO

	-- Create Table Departments
IF OBJECT_ID ('Deparments', 'U') IS NOT NULL
   DROP TABLE Departments
GO
CREATE TABLE Departments
(
	ID int IDENTITY(1,1) NOT NULL 
		CONSTRAINT pk_Department_ID PRIMARY KEY (ID),
	DepartmentName nvarchar(50),
	Address nvarchar(100),
	TotalOfFloor int,
	Phone char(24)

)
-- Create Table Areas
IF OBJECT_ID ('Areas', 'U') IS NOT NULL
   DROP TABLE Areas
GO
CREATE TABLE Areas
(
	ID int IDENTITY(1,1) NOT NULL 
		CONSTRAINT pk_Area_ID PRIMARY KEY (ID),
	AreaName nvarchar(50),
	DepartmentID int
		CONSTRAINT	fk_Areas_Departments FOREIGN KEY (DepartmentID)REFERENCES Departments(ID) ON UPDATE CASCADE,
	TotalOfFloor int,
	Phone char(24)

)
-- Create Table Teamplates
IF OBJECT_ID ('Teamplates', 'U') IS NOT NULL
   DROP TABLE Teamplates
GO
CREATE TABLE Teamplates
(
	ID int IDENTITY(1,1) NOT NULL 
		CONSTRAINT pk_Teamplate_ID PRIMARY KEY (ID),
	TeamplateName nvarchar(50),
	[value] FLOAT,
	AreaHouse INT

)
-- Create Table Apartments
IF OBJECT_ID ('Apartments', 'U') IS NOT NULL
   DROP TABLE Apartments
GO
CREATE TABLE Apartments
(
	ID int IDENTITY(1,1) NOT NULL 
		CONSTRAINT pk_Apartments_ID PRIMARY KEY (ID),
	AreaID int
		CONSTRAINT	fk_Apartments_Areas FOREIGN KEY (AreaID)REFERENCES Areas(ID) ON UPDATE CASCADE,
	TeamplateID int
		CONSTRAINT	fk_Apartments_Teamplates FOREIGN KEY (TeamplateID)REFERENCES Teamplates(ID) ON UPDATE CASCADE,
	ApartmentNumber varchar(30),
	FloorNumber int,
	Status bit
)
-- Create Table Persons
IF OBJECT_ID ('Persons', 'U') IS NOT NULL
   DROP TABLE Persons
GO
CREATE TABLE Persons
(
	ID int IDENTITY(1,1) NOT NULL 
		CONSTRAINT pk_Person_ID PRIMARY KEY (ID),
	LastName nvarchar(30),
	FirstName nvarchar(15),
	Phone char(24),
	Identify char(15)
)
-- Create Table Customers
IF OBJECT_ID ('Customers', 'U') IS NOT NULL
   DROP TABLE Customers
GO
CREATE TABLE Customers
(
	ID int IDENTITY(1,1) NOT NULL
		CONSTRAINT pk_Customers_ID PRIMARY KEY(ID),
	PersonID int 
		CONSTRAINT fk_Customers_Persons FOREIGN KEY (PersonID)REFERENCES Persons(ID)ON UPDATE CASCADE,
	ApartmentID int
		CONSTRAINT fk_Customers_Apartments FOREIGN KEY (ApartmentID)REFERENCES Apartments(ID)ON UPDATE CASCADE,
	Host bit
)
-- Create Table Contracts
IF OBJECT_ID ('Contracts', 'U') IS NOT NULL
   DROP TABLE Contracts
GO
CREATE TABLE Contracts
(
	ID int IDENTITY(1,1) NOT NULL
		CONSTRAINT pk_Contracts_ID PRIMARY KEY(ID),
	ContractType int,
	PersonID int 
		CONSTRAINT fk_Contracts_Persons FOREIGN KEY (PersonID)REFERENCES Persons(ID)ON UPDATE CASCADE,
	ApartmentID int
		CONSTRAINT fk_Contracts_Apartments FOREIGN KEY (ApartmentID)REFERENCES Apartments(ID)ON UPDATE CASCADE,
	SigningDate smalldatetime,
	HiringDate smalldatetime,
	EndDate smalldatetime,
	PurchaseDate smalldatetime,
	ReceivedOfDate smalldatetime
	
)
-- Create Table Offices
IF OBJECT_ID ('Offices', 'U') IS NOT NULL
   DROP TABLE Offices
GO
CREATE TABLE Offices
(
	ID int IDENTITY(1,1) NOT NULL
		CONSTRAINT pk_Offices_ID PRIMARY KEY(ID),
	DepartmentID int 
		CONSTRAINT fk_Offices_Departments FOREIGN KEY (DepartmentID)REFERENCES Departments(ID)ON UPDATE CASCADE,
	OfficeName nvarchar(30),
	Phone char(24),
	Address nvarchar(100)
)

-- Create Table Employees
IF OBJECT_ID ('Employees', 'U') IS NOT NULL
   DROP TABLE Employees
GO
CREATE TABLE Employees
(
	ID int IDENTITY(1,1) NOT NULL
		CONSTRAINT pk_Employees_ID PRIMARY KEY (ID),
	PersonID int 
		CONSTRAINT fk_Employees_Persons FOREIGN KEY (PersonID)REFERENCES Persons(ID)ON UPDATE CASCADE,
	OfficeID int 
		CONSTRAINT fk_Employees_Offices FOREIGN KEY(OfficeID)REFERENCES Offices(ID)ON UPDATE CASCADE
		
)

-- Create Table Equipments
IF OBJECT_ID ('Equipments', 'U') IS NOT NULL
   DROP TABLE Equipments
GO
CREATE TABLE Equipments
(
	ID int IDENTITY(1,1) NOT NULL
		CONSTRAINT pk_Equipments_ID PRIMARY KEY (ID),
	EquipmentName nvarchar(50),
	MaintainDate smalldatetime,
	ProducDate smalldatetime,
	EnpiratyDate smalldatetime
)
-- Create Table EquipmentDetails
IF OBJECT_ID ('EquipmentDetails', 'U') IS NOT NULL
   DROP TABLE EquipmentDetails
GO
CREATE TABLE EquipmentDetails
(
	ID int IDENTITY(1,1) NOT NULL
		CONSTRAINT pk_EquipmentDetails_ID PRIMARY KEY (ID),
	EquipmentID int
		CONSTRAINT fk_EquipmentDetails_Equipments FOREIGN KEY (EquipmentID)REFERENCES Equipments(ID)ON UPDATE CASCADE,
	TeamplateID int
		CONSTRAINT fk_EquipmentDetails_Teamplates FOREIGN KEY (TeamplateID)REFERENCES Teamplates(ID) ON UPDATE CASCADE
)
-- Create Table EquipmentHistories
IF OBJECT_ID ('EquipmentHistories', 'U') IS NOT NULL
   DROP TABLE EquipmentHistories
GO
CREATE TABLE EquipmentHistories
(
	ID int IDENTITY(1,1) NOT NULL
		CONSTRAINT pk_EquipmentHistories_ID PRIMARY KEY (ID),
	EDID int
		CONSTRAINT fk_EquipmentHistories_EquipmentDetails FOREIGN KEY (EDID)REFERENCES EquipmentDetails(ID)ON UPDATE CASCADE,
	EmployeeID int
		CONSTRAINT fk_EquipmentHistories_Employees FOREIGN KEY (EmployeeID)REFERENCES Employees(ID) ,
	ApartmentID int
		CONSTRAINT fk_EquipmentHistories_Apartments FOREIGN KEY (ApartmentID)REFERENCES Apartments(ID) ,
	MaintainDate smalldatetime,
	Replate BIT,
	repair BIT,	
	Descriptions ntext
		
)


-- Create Table Vehicles
IF OBJECT_ID ('Vehicles', 'U') IS NOT NULL
   DROP TABLE Vehicles
GO
CREATE TABLE Vehicles
(
	ID int IDENTITY(1,1) NOT NULL 
		CONSTRAINT pk_Vehicles_ID PRIMARY KEY (ID),
	PersonID int
		CONSTRAINT fk_Vehicles_Persons FOREIGN KEY (PersonID)REFERENCES Persons(ID) ON UPDATE CASCADE,
	RegistryDate smalldatetime,
	CancelDate smalldatetime,
	ParkingPlace nvarchar(50),
	Type nvarchar(20)

)
-- Create Table Forms
IF OBJECT_ID ('Forms', 'U') IS NOT NULL
   DROP TABLE Forms
GO
CREATE TABLE Forms
(
	ID int IDENTITY(1,1) NOT NULL 
		CONSTRAINT pk_Forms_ID PRIMARY KEY (ID),
	FormsName nvarchar(30)

)


-- Create Table Roles
IF OBJECT_ID ('Roles', 'U') IS NOT NULL
   DROP TABLE Roles
GO
CREATE TABLE Roles
(
	ID int IDENTITY(1,1) NOT NULL 
		CONSTRAINT pk_Roles_ID PRIMARY KEY (ID),
	RoleName nvarchar(30),
	Description ntext

)

-- Create Table RoleDetails
IF OBJECT_ID ('RoleDetails', 'U') IS NOT NULL
   DROP TABLE RoleDetails
GO
CREATE TABLE RoleDetails
(
	ID int IDENTITY(1,1) NOT NULL 
		CONSTRAINT pk_RoleDetails_ID PRIMARY KEY (ID),
	RoleID int
		CONSTRAINT fk_RoleDetails_Roles FOREIGN KEY (RoleID)REFERENCES Roles(ID) ON UPDATE CASCADE,
	FormID int
		CONSTRAINT fk_RoleDetails_Forms FOREIGN KEY (FormID)REFERENCES Forms(ID) ON UPDATE CASCADE,
	Ins bit,
	Del bit,
	Upd bit,
	Vie bit,
	Al bit	

)

-- Create Table Users
IF OBJECT_ID ('Users', 'U') IS NOT NULL
   DROP TABLE Users
GO
CREATE TABLE Users
(
	EmployeeID int
		CONSTRAINT fk_Users_Employees FOREIGN KEY (EmployeeID)REFERENCES Employees(ID) ON UPDATE CASCADE,
	RoleID int
		CONSTRAINT fk_Users_Roles FOREIGN KEY (RoleID)REFERENCES Roles(ID) ON UPDATE CASCADE,
	UserName nvarchar(50) unique,
	Password nvarchar(30),
	Active bit
	
)